#!/usr/bin/python
#coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util
import sys


dtgC45s = """
    CREATE TABLE BGTARAS1.PREQ112_DTG_C45S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI_FLOW_1_PV DECIMAL(24,6),
        HUMI_H2_1_PV DECIMAL(24,6),
        HUMI_FLOW_2_PV DECIMAL(24,6),
        HUMI_H2_2_PV DECIMAL(24,6),
        HUMI_FLOW_3_PV DECIMAL(24,6),
        HUMI_H2_3_PV DECIMAL(24,6),
        HUMI_FLOW_4_PV DECIMAL(24,6),
        HUMI_H2_4_PV DECIMAL(24,6),
        HUMI_FLOW_5_PV DECIMAL(24,6),
        HUMI_H2_5_PV DECIMAL(24,6),
        HUMI_FLOW_6_PV DECIMAL(24,6),
        HUMI_H2_6_PV DECIMAL(24,6),
        HUMI_FLOW_7_PV DECIMAL(24,6),
        HUMI_H2_7_PV DECIMAL(24,6),
        HUMI_FLOW_8_PV DECIMAL(24,6),
        HUMI_H2_8_PV DECIMAL(24,6),
        humi_dew_1_pv_avg DECIMAL(24,6),
        humi_dew_2_pv_avg DECIMAL(24,6),
        humi_dew_3_pv_avg DECIMAL(24,6),
        humi_dew_4_pv_avg DECIMAL(24,6),
        humi_dew_5_pv_avg DECIMAL(24,6),
        HUMI2_LIQUID_TEMP DECIMAL(24,6),
        HUMI3_LIQUID_TEMP DECIMAL(24,6),
        HUMI4_LIQUID_TEMP DECIMAL(24,6),
        HUMI5_LIQUID_TEMP DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""



def createDTG_C45S(start_time,end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ112_DTG_C45S' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0 :
        conn_rds.execute("drop table BGTARAS1.PREQ112_DTG_C45S")
    else:
        conn_rds.execute(dtgC45s)

    sql = """
        WITH
        tmp AS
        (
            SELECT
                a.unit_code,
                a.PRODUCE_TIME,
                a.CLASS,
                a.SHIFT,
                a.PROD_COILNO,
                a.ENTRY_COILNO,
                B.START_TIME AS START_TIME,
                B.END_TIME   AS END_TIME
            FROM
                BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                BGTAROQ112.T_ODS_Q112_BASE b
            WHERE
                a.UNIT_CODE='Q112'
            AND b.PRD_COILNO=RIGHT(a.PROD_COILNO,9)
            AND a.PRODUCE_TIME BETWEEN """+start_time+""" AND """+end_time+"""
        )
        SELECT
                a.unit_code,
                a.PRODUCE_TIME,
                a.CLASS,
                a.SHIFT,
                a.PROD_COILNO,
                a.ENTRY_COILNO,
                AVG(CAST(C.HUMI_FLOW_1_PV AS DOUBLE)) AS HUMI_FLOW_1_PV,
                AVG(CAST(C.HUMI_FLOW_2_PV AS DOUBLE)) AS HUMI_FLOW_2_PV,
                AVG(CAST(C.HUMI_FLOW_3_PV AS DOUBLE)) AS HUMI_FLOW_3_PV,
                AVG(CAST(C.HUMI_FLOW_4_PV AS DOUBLE)) AS HUMI_FLOW_4_PV,
                AVG(CAST(C.HUMI_FLOW_5_PV AS DOUBLE)) AS HUMI_FLOW_5_PV,
                AVG(CAST(C.HUMI_FLOW_6_PV AS DOUBLE)) AS HUMI_FLOW_6_PV,
                AVG(CAST(C.HUMI_FLOW_7_PV AS DOUBLE)) AS HUMI_FLOW_7_PV,
                AVG(CAST(C.HUMI_FLOW_8_PV AS DOUBLE)) AS HUMI_FLOW_8_PV,
                AVG(CAST(C.HUMI_H2_1_PV AS DOUBLE)) AS HUMI_H2_1_PV,
                    AVG(CAST(C.HUMI_H2_2_PV AS DOUBLE)) AS HUMI_H2_2_PV,
                    AVG(CAST(C.HUMI_H2_3_PV AS DOUBLE)) AS HUMI_H2_3_PV,
                    AVG(CAST(C.HUMI_H2_4_PV AS DOUBLE)) AS HUMI_H2_4_PV,
                    AVG(CAST(C.HUMI_H2_5_PV AS DOUBLE)) AS HUMI_H2_5_PV,
                    AVG(CAST(C.HUMI_H2_6_PV AS DOUBLE)) AS HUMI_H2_6_PV,
                    AVG(CAST(C.HUMI_H2_7_PV AS DOUBLE)) AS HUMI_H2_7_PV,
                    AVG(CAST(C.HUMI_H2_8_PV AS DOUBLE)) AS HUMI_H2_8_PV,
                    AVG(CAST(C.HUMI_DEW_1_PV AS DOUBLE)) AS HUMI_H2_1_PV,
                    AVG(CAST(C.HUMI_DEW_2_PV AS DOUBLE)) AS HUMI_H2_2_PV,
                    AVG(CAST(C.HUMI_DEW_3_PV AS DOUBLE)) AS HUMI_H2_3_PV,
                    AVG(CAST(C.HUMI_DEW_4_PV AS DOUBLE)) AS HUMI_H2_4_PV,
                    AVG(CAST(C.HUMI_DEW_5_PV AS DOUBLE)) AS HUMI_H2_5_PV,
                    AVG(CAST(C.HUMI2_LIQUID_TEMP AS DOUBLE)) AS HUMI2_LIQUID_TEMP,
                    AVG(CAST(C.HUMI3_LIQUID_TEMP AS DOUBLE)) AS HUMI3_LIQUID_TEMP,
                    AVG(CAST(C.HUMI4_LIQUID_TEMP AS DOUBLE)) AS HUMI4_LIQUID_TEMP,
                    AVG(CAST(C.HUMI5_LIQUID_TEMP AS DOUBLE)) AS HUMI5_LIQUID_TEMP
            FROM
                tmp a
            LEFT JOIN
                BGTAROQ112.T_ODS_DTG_C45S c
            ON
                c.UP_DATE BETWEEN a.START_TIME AND a.END_TIME
            GROUP BY
                a.unit_code,
                a.PRODUCE_TIME,
                a.CLASS,
                a.SHIFT,
                a.PROD_COILNO,
                a.ENTRY_COILNO
    """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ112_DTG_C45S", "BGTARAS1")
    # conn_rds.execute("drop table BGTARAS1.PREQ112_DTG_C45S")


def DTG_C45S(start_time, end_time):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    createDTG_C45S(start_time, end_time)
    conn_rds.close()

